<?php

namespace app\admin\service;


use app\admin\model\User;
use think\Db;

/**
 * 数据中心-BI大屏-服务类
 * Class DeviceBindService
 * @package app\admin\service
 */
class DataCenterService extends BaseService
{
    public $schooleId;
    public $commonWhere;
    public $success;

    /**
     * 构造函数
     */
    public function __construct($schooleId=0, $userId = 0)
    {
        $userSchooleId = db('user')->where('id', $userId)->value('schoole_id');
        $this->schooleId = $schooleId ? $schooleId : ($userSchooleId ? $userSchooleId : 19);
        $this->commonWhere[] = ['schoole_id', '=', $this->schooleId];
        $this->success = [
            "msg" => '请求成功',
            "code" => 0
        ];
    }

    /**
     * 获取平台基础信息
     * @return void
     */
    public function getBaseInfo()
    {
        $school = db('schoole')
            ->where('id', $this->schooleId)
            ->where('mark', 1)
            ->find();

        $teacher_count = db('teacher')
            ->where($this->commonWhere)
            ->where('mark', 1)
            ->count('id');
        $student_count = db('student')
            ->where($this->commonWhere)
            ->where('mark', 1)
            ->count('id');
        $class_count = db('class')
            ->where($this->commonWhere)
            ->where('mark', 1)
            ->count('id');

        $weatherInfo = $this->getWeatherInfo($school['district_code'] ?? '');

        $this->success['data'] = [
	        'location'=> explode(',', $school['location']),
            'school_name'=> $school['title'] ?? '',
            'teacher_count'=> $teacher_count ?: 0,
            'student_count'=> $student_count ?: 0,
            'class_count'=> $class_count ?: 0,
            'total_count'=> $teacher_count + $student_count,
            'weatherInfo'=> $weatherInfo,
        ];

        return $this->success;
    }

    /**
     * 获取今日学生出勤信息
     * @return void
     */
    public function getTodayAttendance()
    {
        $where1[] = ['schoole_id', '=', $this->schooleId];
        $where1[] = ['mark', '=', 1];
        $count = db('student') ->where($where1)->count();
        // 学生在校离校信息
        $start = date('Y-m-d 00:00:00');
        $start = strtotime($start);
        $end = date('Y-m-d 23:59:59');
        $end = strtotime($end);
        $where[] = ['t.schoole_id', '=', $this->schooleId];
        $where[] = ['t.status', '=', 1];
        $where[] = ['t.mark', '=', 1];
        $student_list = db('student')
            ->alias('t')
            ->field('t.id,CASE WHEN sl.id IS NOT NULL THEN 1 ELSE 0 END AS in_school,d.is_active')
            ->where($where)
            ->where('school_arrive_time','>',$start)
            ->where('school_arrive_time','<',$end)
            ->join('student_work_log sl', 'sl.student_id = t.id', 'left')
            ->join('device_imei d', 'd.imei = t.imei')
            ->group('sl.student_id')
            ->select();

        $data = [
            'student_in_school_count'       => 0,
            'student_leave_school_count'    => 0,
            'student_active_count'          => 0,
            'student_no_active_count'       => 0
        ];
        foreach ($student_list as $student){
            if($student['in_school']){
                $data['student_in_school_count']++;
            }else{
                $data['student_leave_school_count']++;
            }
            if($student['is_active']){
                $data['student_active_count']++;
            }else{
                $data['student_no_active_count']++;
            }
        }

        $rate1 = $data['student_in_school_count'] ? (float_two($data['student_in_school_count'] / $count )*100).'%' : '0%';
        $student_leave_school_count = $count - $data['student_in_school_count'];
        $rate2 =  $student_leave_school_count ? float_two($student_leave_school_count / $count )*100 .'%' : '0%';

        $inschoolCount = db('device_location')
            ->alias('dl')
            ->field('max(dl.id) id,dl.lng,dl.lat')
            ->join('student s', 's.imei = dl.imei and s.mark = 1 and s.schoole_id='.$this->schooleId)
            ->join('think_device_online o','dl.imei = o.imei')
            ->where('o.online=1')
            ->order('dl.id desc')
            //->whereTime('dl.create_time', 'd')
            ->group('dl.imei')
            ->count();
        $student_no_active_count = $count - $inschoolCount;
        $this->success['data'] = [
            ['name'=> '在校人数', 'count'=> $data['student_in_school_count'], 'rate'=> $rate1],
            ['name'=> '离校人数', 'count'=> $student_leave_school_count, 'rate'=> $rate2],
            ['name'=> '在线人数', 'count'=> $inschoolCount, 'rate'=> $inschoolCount ? round($inschoolCount/$count * 100) .'%' : '0%'],
            ['name'=> '离线人数', 'count'=> $student_no_active_count, 'rate'=> $student_no_active_count ? round($student_no_active_count / $count * 100) .'%' : '0%'],
        ];

        return $this->success;
    }

    /**
     * 获取学生报警信息列表
     * @param $page
     * @param $page_size
     * @return void
     */
    public function getStudentResortList($page=1, $page_size=10)
    {
        $list = db('resort_log')
            ->alias('r')
            ->field('r.id,r.student_id,r.da_type,dt.name as da_type_name,r.status,r.create_time,t.name,t.grade_id,t.class_id,g.title as grade_name,c.title as class_name')
            ->where('t.schoole_id', $this->schooleId)
            ->join('device_alarm_type dt', 'dt.id = r.da_type', 'left')
            ->join('student t', 't.id = r.student_id')
            ->join('grade g', 'g.id = t.grade_id', 'left')
            ->join('class c', 'c.id = t.class_id', 'left')
            ->order('r.id desc')
            ->paginate($page_size)
            ->each(function ($item) {
                $item['create_time'] = date('Y-m-d H:i:s', $item['create_time']);
                return $item;
            });

        $this->success['data'] = [
            'total'=> $list->total(),
            'list'=> $list->items()
        ];

        return $this->success;
    }

    /**
     * 学生报警统计
     * @return array
     */
    public function getStudentResortTotal()
    {
        $list = db('resort_log')
            ->alias('r')
            ->field('r.id,r.student_id,r.status')
            ->where('t.schoole_id', $this->schooleId)
            ->join('student t', 't.id = r.student_id')
            ->select();

        $total_count = count($list);
        $handle = 0;
        $no_handle = 0;
        foreach ($list as $item) {
            if ($item['status'] == 1) {
                $handle++;
            }else{
                $no_handle++;
            }
        }
        if($total_count == 0){
            $rate = '0%';
        }else{
            $rate = round(($handle / $total_count * 100), 2). '%';
        }
        $this->success['data'] = [
            'handle'=> $handle,
            'no_handle'=> $no_handle,
            'handle_rate'=>$rate,
        ];

        return $this->success;
    }

    /**
     * 今日各年级平均出勤率
     *
     * @return array
     */
    public function getTodayGradeAttendance()
    {
        $where[] = ['schoole_id', '=', $this->schooleId];
        $where[] = ['mark', '=', 1];
        // 年级
        $grade_list = db('grade')
            ->field('id,title,grade_key')
            ->order('grade_key', 'asc')
            ->where($where)
            ->select();

        $t1       = strtotime(date('Y-m-d'));
        $t2       = strtotime(date('Y-m-d 23:59'));
        $where2[] = ['school_arrive_time', ['>', $t1], ['<', $t2]];
        foreach ($grade_list as $k => $item) {
            $where3   = [];
            $where3[] = ['schoole_id', '=', $this->schooleId];
            $where3[] = ['grade_id', '=', $item['grade_key']];
            $where3[] = ['mark', '=', 1];
            $ids      = Db::table('think_student')->where($where3)->field('id')->select();
            if (empty($ids)) {
                $grade_list[$k]['avg_temperature'] = '0';
            } else {
                $count1                            = count($ids);
                $count2                            = Db::table('think_student_work_log')
                    ->where($where2)
                    ->where('student_id', 'in', array_column($ids, 'id'))
                    ->count();
                $grade_list[$k]['avg_temperature'] = (float_two($count2 / $count1) * 100);
            }
        }
        $this->success['data'] = $grade_list;

        return $this->success;
    }

    /**
     * 当日学生体温/心率监测数据
     * @return void
     */
    public function getTodayMonitor()
    {
        $grade_list = db('grade')
            ->field('id,title')
            ->where($this->commonWhere)
            ->where('mark', 1)
            ->order('sort', 'asc')
            ->select();
        foreach ($grade_list as $k=>$item){
            // 年级学生体温平均值
           /* $student_ids = db('student')
                ->where(['grade_id'=> $item['id'], 'status'=> 1])
                ->column('id');
            $avg_temperature = db('temperature_log')
                ->whereIn('student_id', $student_ids)
                ->avg('temperature');
            $grade_list[$k]['avg_temperature'] = $avg_temperature ? round($avg_temperature, 2) : '36.5';*/
            $grade_list[$k]['avg_temperature'] = 0;
            // 年级学生心率平均值
          /*  $avg_heart_rate = db('heart_rate_log')
                ->whereIn('student_id', $student_ids)
                ->avg('heart_rate');
            $grade_list[$k]['avg_heart_rate'] = $avg_heart_rate ? round($avg_heart_rate) : mt_rand(90,100);*/
            $grade_list[$k]['avg_heart_rate'] = 0;
        }
        $this->success['data'] = $grade_list;

        return $this->success;
    }

    /**
     * 各年级平均周/月/年出勤率统计
     * @return void
     */
    public function getTotalGradeAttendance()
    {
        $data['week'] = $this->getGradeAttendance();
        $data['month'] = $this->getGradeAttendance('month');
        $data['year'] = $this->getGradeAttendance('year');
        $this->success['data'] = $data;
        return $this->success;
    }

    /**
     * 学校当日设备在线分布
     * @return void
     */
    public function getTodayDeviceDistribution()
    {
        $list = db('device_location')
            ->alias('dl')
            ->field('max(dl.id) id,dl.lng,dl.lat')
            ->join('student s', 's.imei = dl.imei and s.mark = 1 and s.schoole_id='.$this->schooleId)
            ->join('think_device_online o','dl.imei = o.imei')
            ->where('o.online=1')
            ->order('dl.id desc')
            //->whereTime('dl.create_time', 'd')
            ->group('dl.imei')
            ->select();
        foreach ($list as &$item){
            unset($item['id']);
        }
        $this->success['data'] = [
            'total'=> count($list),
            'list'=> $list
        ];

        return $this->success;
    }

    /**
     * 获取各年级出勤统计 本周 本月 本年
     * @param $type
     * @return array
     */
    private function getGradeAttendance($type='week')
    {
        $where[] = ['g.schoole_id', '=', $this->schooleId];
        $where[] = ['g.mark', '=', 1];
        // 年级
        $list = db('grade')
            ->alias('g')
            ->field('g.id,g.title,grade_key')
            ->where($where)
            ->order('g.sort', 'asc')
            ->select();

        foreach ($list as $k=>$item){
            if($type=='week'){
                $t1 = strtotime(date('Y-m-d', strtotime("this week")));
                $t2 = strtotime(date('Y-m-d 23:59', strtotime("this week +6 days")));
                $where2 = [];
                $where2[] = ['belong_time',['>',$t1],['<',$t2]];
                $where3 = [
                    'schoole_id' => $this->schooleId,
                    'grade_id' => $item['grade_key'],
                ];
                $a = Db::table('think_grade_attendance')->where($where3)->where($where2)->sum('attendance');
                $c = Db::table('think_grade_attendance')->where($where3)->where($where2)->count();
            }
            if($type=='month'){
                $where3 = [
                    'schoole_id' => $this->schooleId,
                    'grade_id' => $item['grade_key'],
                    'month' => date('Ym'),
                ];
                $a = Db::table('think_grade_attendance')->where($where3)->sum('attendance');
                $c = Db::table('think_grade_attendance')->where($where3)->count();
            }
            if($type=='year'){
                $where3 = [
                    'schoole_id' => $this->schooleId,
                    'grade_id' => $item['grade_key'],
                    'year' => date('Y'),
                ];
                $a = Db::table('think_grade_attendance')->where($where3)->sum('attendance');
                $c = Db::table('think_grade_attendance')->where($where3)->count();
            }
            if($c == 0){
                $attendance = 0;
            }else{
                $attendance = float_two($a/$c);
            }
            $list[$k]['attendance'] = $attendance.'%';
        }

        return $list;
    }

    /**
     * 获取各年级出勤统计 本周 本月 本年
     * @param $type
     * @return array
     */
    private function getGradeAttendance2($type='week')
    {
        switch ($type){
            case 'week':
                //$join_and_where = 'week = '.date('W');
                $t1 = strtotime(date('Y-m-d', strtotime("this week")));
                $t2 = strtotime(date('Y-m-d 23:59', strtotime("this week +6 days")));
                $where2[] = ['school_arrive_time',['>',$t1],['<',$t2]];
                break;
            case 'month':
                $t1 = strtotime(date('Y-m-01'));
                $t2 = strtotime('+1 month',$t1)-1  ;
                $where2[] = ['school_arrive_time',['>',$t1],['<',$t2]];
                break;
            case 'year':
                $t1 = strtotime(date('Y-01-01'));
                $t2 = strtotime( date('Y-12-31 23:59'));
                $where2[] = ['school_arrive_time',['>',$t1],['<',$t2]];
                break;
            default:
                return [];
        }
        $where[] = ['g.schoole_id', '=', $this->schooleId];
        $where[] = ['g.mark', '=', 1];
        // 年级
        $list = db('grade')
            ->alias('g')
            ->field('g.id,g.title,grade_key')
            ->where($where)
            ->order('g.sort', 'asc')
            ->select();

        foreach ($list as $k=>$item){
            //$list[$k]['attendance'] = (round($item['attendance'], 2)*100).'%';
      /*      $where3 = [];
            $where3[] = ['schoole_id', '=', $this->schooleId];
            $where3[] = ['grade_id', '=',$item['grade_key']];
            $where3[] = ['mark', '=',1];
            $count = Db::table('think_student')->where($where3)->count();
            if($count == 0){
                $list[$k]['attendance'] ='0%';
            }else{
                $where3[] = ['schoole_id', '=', $this->schooleId];
                $where3[] = ['grade_id', '=',$item['grade_key']];
                $where3[] = ['mark', '=',1];
                $count = Db::table('think_student')->alias('s')->where($where3)->join('think_student_work_log tl', 's.id = tl.student_id')->where($where2)->count();
                $list[$k]['attendance'] ='0%';
            }*/
            $list[$k]['attendance'] ='0%';
        }
        return $list;
    }

    private function getWeatherInfo($city='西安市')
    {

        // 高德API Key
        $key = env('gaode.web_gaode_map_key');
        // 构造请求URL
        $url = 'https://restapi.amap.com/v3/weather/weatherInfo?key=' . $key . '&city=' . urlencode($city);

        // 使用file_get_contents发送请求
        $response = file_get_contents($url);

        // 对返回的JSON格式数据进行解码
        $data = json_decode($response, true);
        $result = [];
        // 输出天气信息
        if ($data && $data['status'] === '1') {
            $result = [
                'weather'=> $data['lives'][0]['weather'], //天气 晴
                'temperature'=> $data['lives'][0]['temperature'], // 实时气温，单位：摄氏度
                'winddirection'=> $data['lives'][0]['winddirection'], //风向 东南
                'windpower'=> $data['lives'][0]['windpower'], //风力级别，单位：级
                'humidity'=> $data['lives'][0]['humidity'], //空气湿度
            ];
        }

        return $result;
    }
}

